<?php

if (isset($_POST['submit'])) {
    $kind = $_POST['kind'];
    $year_from = $_POST['year_from'];
    $year_to = $_POST['year_to'];
    $month_from = $_POST['month_from'];
    $month_to = $_POST['month_to'];
    /* kiểm tra lỗi nhập thông tin vào */
    /* lỗi không nhập khoảng thời gian */
    if ($year_from == -1 || $year_to == -1 || $month_from == -1 || $month_to == -1)
        echo ("Bạn phải nhập khoảng thời gian.");
    else {
        /* lỗi khoảng thời gian không hợp lệ */
        if (($year_from > $year_to) || ($year_from == $year_to && $month_from > $month_to))
            echo ("Khoảng thời gian không hợp lệ.");
        else {
            if ($month_to < 12)
                $month_to = $month_to + 1;
            else {
                $month_to = 1;
                $year_to = $year_to + 1;
            }
            $query_1 = "SELECT mobilephone.mobileid, model, mobilephone.price as gia, sum (orders.quantity) as soluong, sum (orders.quantity * price) as giatri
                                  FROM orders, mobilephone
                                  WHERE (orders.mobileid = mobilephone.mobileid)
                                        AND (date >= '" . $year_from . "-" . $month_from . "-01')
                                        AND (date < '" . $year_to . "-" . $month_to . "-1')
                                  GROUP BY mobilephone.mobileid, model, mobilephone.price
                                  ORDER BY giatri desc";
            $query_2 = "select mobilephone.mobileid, model, mobilephone.price as gia, sum (provides.quantity) as soluong, sum (provides.quantity*provides.price) as giatri
                                      from mobilephone, provides
                                      where (mobilephone.mobileid = provides.mobileid)
                                      and (\"date\" >= '" . $year_from . "-" . $month_from . "-1')
                                      and (\"date\" < '" . $year_to . "-" . $month_to . "-1')
                                      group by mobilephone.mobileid, model, mobilephone.price, mobilephone.quantity
                                      order by giatri desc";
            echo "Kết quả thống kê<br/>";
            if ($kind == 1 || $kind == 2) { /* doanh số mua vào, bán ra */
                if ($kind == 1) {
                    $query = $query_1;
                } else {
                    $query = $query_2;
                }
                $result = pg_query($connection, $query);
                echo "<table>\n";
                echo "<tr><th>STT</th><th>Model</th><th>Đơn giá</th><th>Số lượng</th><th>Giá trị</th></tr>";
                for ($i = 1; $i <= pg_num_rows($result); $i++) {
                    echo "<tr><td>$i</td><td>";
                    echo pg_fetch_result($result, $i - 1, 'model') . "</td><td>";
                    echo number_format(pg_fetch_result($result, $i - 1, 'gia'), 3, ',', ',') . " VND</td><td>";
                    echo pg_fetch_result($result, $i - 1, 'soluong') . "</td><td>";
                    echo number_format(pg_fetch_result($result, $i - 1, 'giatri'), 3, ',', ',') . " VND</td></tr>";
                }
                echo "</table>";
                $sum = 0;
                for ($row = 0; $row < pg_num_rows($result); $row++) {
                    $sum += pg_fetch_result($result, $row, 'giatri');
                }
                echo "<h4>Tổng giá trị: " . number_format($sum, 3, ',', ',') . " đồng</h4>";
            } else if ($kind == 3)/* thống kê doanh thu, lãi lỗ  */ {
                $result = pg_query($connection, $query_1);
                $thu = 0;
                for ($row = 0; $row < pg_num_rows($result); $row++) {
                    $thu += pg_fetch_result($result, $row, 'giatri');
                }
                $result = pg_query($connection, $query_2);
                $chi = 0;
                for ($row = 0; $row < pg_num_rows($result); $row++) {
                    $chi += pg_fetch_result($result, $row, 'giatri');
                }
                echo "<table>";
                echo "<tr><th>Danh mục</th><th>Giá trị</th></tr>";
                echo "<tr><td width = 100>Số thu</td><td>";
                echo number_format($thu, 3, ',', ',') . " VNĐ</td></tr>";
                echo "<tr><td>Số chi</td><td>";
                echo number_format($chi, 3, ',', ',') . " VNĐ</td></tr>";
                if ($thu > $chi) {
                    echo "<tr><td>Lãi</td><td>";
                    echo number_format($thu - $chi, 3, ',', ',') . " đồng</td></tr>";
                } else {
                    echo "<tr><td>Lỗ</td><td>";
                    echo number_format($chi - $thu, 3, ',', ',') . " đồng</td></tr>";
                }
                echo "</table>";
            } else if ($kind == 4) /* sản phẩm bán được nhiều nhất */ {
                $query = "select mobilephone.*, sum (orders.quantity) as soluong
                                          from mobilephone, orders
                                          where (mobilephone.mobileid = orders.mobileid)
                                                and (\"date\" >= '" . $year_from . "-" . $month_from . "-1')
                                                and (\"date\" < '" . $year_to . "-" . $month_to . "-1')
                                          group by mobilephone.mobileid, model, price, mobilephone.quantity, promotions, warranty, os, entertainment, network, screen, battery, appearance, picture_path
                                          having sum (orders.quantity) >= all (select sum (quantity)
                                                                                from orders
                                                                                where (\"date\" >= '" . $year_from . "-" . $month_from . "-1')
                                                                                        and (\"date\" < '" . $year_to . "-" . $month_to . "-1')
                                                                                group by mobileid
                                                                                    )";

                $result = pg_query($connection, $query);
                /* in thông tin về điện thoại */
                echo "<div style=\"float: left; width: 400px; height: 600px\">";
                echo "<table width: 400px;\">";
                if (pg_num_rows($result) == 0)
                    echo "<tr><td>Không có sản phẩm nào được bán trong thời gian này</td></tr>";
                else {
                    echo "<tr><td><h4>Số lượng bán được:</h4></td><td><h4>" . pg_fetch_result($result, 0, 'soluong') . " chiếc</h4></td></tr>";
                    echo "<tr><td>ID: </td><td>" . pg_fetch_result($result, 0, 'mobileid') . "</td></tr>";
                    echo "<tr><td>Model: </td><td><div class=\"tb_img\"><a href=\"show_mobile.php?mbname=" . pg_fetch_result($result, 0, 'model') . "\"><img style=\"height:120px;width:auto;\" src=\"" . pg_fetch_result($result, 0, 'picture_path') . "\"/><div class=\"mb_name\" style=\"text-align:left;\">" . pg_fetch_result($result, 0, 'model') . "</div></a></div></td></tr>";
                    echo "<tr><td>Giá: </td><td><b>" . number_format(pg_fetch_result($result, 0, 'price'), 3, ',', ',') . " VND</td></tr>";
                    echo "<tr><td>Khuyến mại: </td><td><b>" . pg_fetch_result($result, 0, 'promotions') . "</td></tr>";
                    echo "<tr><td>Bảo hành: </td><td><b>" . pg_fetch_result($result, 0, 'warranty') . "</td></tr>";
                    echo "<tr><td>Hệ điều hành: </td><td><b>" . pg_fetch_result($result, 0, 'os') . "</td></tr>";
                    echo "<tr><td>Giải trí: </td><td><b>" . pg_fetch_result($result, 0, 'entertainment') . "</td></tr>";
                    echo "<tr><td>Màn hình: </td><td><b>" . pg_fetch_result($result, 0, 'screen') . "</td></tr>";
                    echo "<tr><td>Pin: </td><td><b>" . pg_fetch_result($result, 0, 'battery') . "</td></tr>";
                    echo "<tr><td>Kiểu dáng: </td><td><b>" . pg_fetch_result($result, 0, 'appearance') . "</td></tr>";
                }
                echo "</table>";
                echo "</div>";
            } else if ($kind == 5) { /* sản phẩm có doanh số lớn nhất */
                $query = "select mobilephone.*, sum (orders.quantity * price) as doanhso
                                              from mobilephone, orders
                                              where (mobilephone.mobileid = orders.mobileid)
                                                    and (\"date\" >= '" . $year_from . "-" . $month_from . "-1')
                                                    and (\"date\" < '" . $year_to . "-" . $month_to . "-1')
                                              group by mobilephone.mobileid, model, price, mobilephone.quantity, promotions, warranty, os, entertainment, network, screen, battery, appearance, picture_path
                                              having sum (orders.quantity * price) >= all (select sum (orders.quantity * price)
                                                                                    from mobilephone, orders
                                                                                    where (mobilephone.mobileid = orders.mobileid)
                                                                                        and (\"date\" >= '" . $year_from . "-" . $month_from . "-1')
                                                                                        and (\"date\" < '" . $year_to . "-" . $month_to . "-1')
                                                                                    group by orders.mobileid
                                                                                   )";
                $result = pg_query($connection, $query);
                /* in thông tin về điện thoại */
                echo "<div style=\"float: left; width: 400px; height: 600px\">";
                echo "<table width: 400px;\">";
                if (pg_num_rows($result) == 0)
                    echo "<tr><td>Không có sản phẩm nào được bán trong thời gian này</td></tr>";
                else {
                    echo "<tr><td><h4>Doanh số:</h4></td><td><h4>" . number_format(pg_fetch_result($result, 0, 'doanhso'), 3, ',', ',') . " VND</h4></td></tr>";
                    echo "<tr><td>ID: </td><td>" . pg_fetch_result($result, 0, 'mobileid') . "</td></tr>";
                    echo "<tr><td>Model: </td><td><div class=\"tb_img\"><a href=\"show_mobile.php?mbname=" . pg_fetch_result($result, 0, 'model') . "\"><img style=\"height:120px;width:auto;\" src=\"" . pg_fetch_result($result, 0, 'picture_path') . "\"/><div class=\"mb_name\" style=\"text-align:left;\">" . pg_fetch_result($result, 0, 'model') . "</div></a></div></td></tr>";
                    echo "<tr><td>Giá: </td><td><b>" . number_format(pg_fetch_result($result, 0, 'price'), 3, ',', ',') . " VND</td></tr>";
                    echo "<tr><td>Khuyến mại: </td><td><b>" . pg_fetch_result($result, 0, 'promotions') . "</td></tr>";
                    echo "<tr><td>Bảo hành: </td><td><b>" . pg_fetch_result($result, 0, 'warranty') . "</td></tr>";
                    echo "<tr><td>Hệ điều hành: </td><td><b>" . pg_fetch_result($result, 0, 'os') . "</td></tr>";
                    echo "<tr><td>Giải trí: </td><td><b>" . pg_fetch_result($result, 0, 'entertainment') . "</td></tr>";
                    echo "<tr><td>Màn hình: </td><td><b>" . pg_fetch_result($result, 0, 'screen') . "</td></tr>";
                    echo "<tr><td>Pin: </td><td><b>" . pg_fetch_result($result, 0, 'battery') . "</td></tr>";
                    echo "<tr><td>Kiểu dáng: </td><td><b>" . pg_fetch_result($result, 0, 'appearance') . "</td></tr>";
                }
                echo "</div>";
                echo "</table>";
            } else if ($kind == 6) { /* Danh sách nhà cung cấp và số lượng hàng bán được */
                $query = "select manufacturer.*, sum (orders.quantity) as soluong, sum (mobilephone.price * orders.quantity) as giatri
            from manufacturer, provides, mobilephone, orders
            where (manufacturer.manuid = provides.manuid)
                 and (provides.mobileid = mobilephone.mobileid)
                 and (mobilephone.mobileid = orders.mobileid)
                 and (orders.\"date\" >= '" . $year_from . "-" . $month_from . "-1')
                 and (orders.\"date\" < '" . $year_to . "-" . $month_to . "-1')
            group by manufacturer.manuid, name, email, address, phone_number
            order by giatri desc";
                $result = pg_query($connection, $query);
                if (pg_num_rows($result)) {
                    echo "<table>";
                    echo "<tr><th>STT</th><th>Nhà cung cấp</th><th>Số lượng bán được</th><th>Giá trị bán được</th>";
                    for ($i = 1; $i <= pg_num_rows($result); $i++) {
                        echo "<tr><td>$i</td><td>";
                        echo pg_fetch_result($result, $i - 1, 'name');
                        echo "</td><td>" . pg_fetch_result($result, $i - 1, 'soluong');
                        echo "</td><td>" . number_format(pg_fetch_result($result, $i - 1, 'giatri'), 3, ',', ',') . " VND";
                        echo "</td></tr>";
                    }
                    echo "</table>";
                }
                else
                    echo "Không có mặt hàng bán được trong thời gian này";
            } else if ($kind == 7) { /* Danh sách khách hàng và số sản phẩm được mua */
                $query = "select customer.customerid, username, email, address, phonenumber, occupation, sum (orders.quantity) as soluong, sum (orders.quantity * price) as giatri
                from customer, orders, mobilephone
                where customer.customerid = orders.customerid
                    and (orders.\"date\" >= '" . $year_from . "-" . $month_from . "-1')
                    and (orders.\"date\" < '" . $year_to . "-" . $month_to . "-1')
                group by customer.customerid, username, email, address, phonenumber, occupation
                order by giatri desc";
                $result = pg_query($connection, $query);
                if (pg_num_rows($result)) {
                    echo "<table>";
                    echo "<tr><th>STT</th><th>Username</th><th>E-mail</th><th>Địa chỉ</th><th>Số điện thoại</th><th>Ngân hàng</th><th>Số lượng mua</th><th>Giá trị mua hàng</th></tr>";
                    for ($i = 1; $i <= pg_num_rows($result); $i++) {
                        echo "<tr><td>$i</td><td>";
                        echo pg_fetch_result($result, $i - 1, 'username') . "</td><td>";
                        echo pg_fetch_result($result, $i - 1, 'email') . "</td><td>";
                        echo pg_fetch_result($result, $i - 1, 'address') . "</td><td>";
                        echo pg_fetch_result($result, $i - 1, 'phonenumber') . "</td><td>";
                        echo pg_fetch_result($result, $i - 1, 'occupation') . "</td><td>";
                        echo pg_fetch_result($result, $i - 1, 'soluong') . "</td><td>";
                        echo number_format(pg_fetch_result($result, $i - 1, 'giatri'), 3, ',', ',') . " VND</td></tr>";
                    }
                    echo "</table>";
                }
                else
                    echo "Không có khách hàng nào trong thời gian này";
            }
            pg_free_result($result);
        }
    }
}
?>